﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Modellayer;
using System.Data.SqlClient;
using System.Data;
using System.ServiceModel;

namespace Dblayer
{
    public class DbShow
    {
        private SqlCommand cmd = null;
        DbMovie dbMovie;
        DbShowRoom dbShowRoom;

        public DbShow()
        {
            DbMovie dbMovie = new DbMovie();
            DbShowRoom dbShowRoom = new DbShowRoom();
        }

        public void createShow(Show show)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = @"insert into show(showid, showtime, showroomnumber, title) values ('" + show.showId + "','" + show.showTime.ToString() + "','" + show.showroom.showRoomNumber + "','" + show.movie.Title + "')";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public Show findShow(int showId)
        {
            string sql = "Select * from show show, Movie movie, Showroom showroom" +
            " where movie.title = show.title" +
            " and showroom.showroomNumber = show.showroomNumber" +
            " and show.showId = '" + showId + "'";
            return BuildShow(sql);
        }

        public List<Show> findShowsByTitleAndDate(string date, string title)
        {
            string sql = "Select * from show show, Movie movie, Showroom showroom" +
            " where movie.title = show.title" +
            " and showroom.showroomNumber = show.showroomNumber" +
            " and show.Title = '" + title + "' and show.ShowTime = '" + date + "'";
            return BuildMoreShow(sql);
        }

        public List<Show> findShowsByDate(string date)
        {
            string sql = "select * from show, movie, showroom" +
            " where movie.title = show.title and " +
            "showroom.showroomnumber = show.showroomnumber and " +
            "show.showtime like '%" + date + "%'";
            return BuildMoreShow(sql);
        }

        public List<string> findDatesByMovie(string title)
        {
            string sql = "select (showtime) from show where title = '" + title + "'";
            IDataReader dbReader;
            List<string> dates = new List<string>();
            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    string date = dbReader["showtime"].ToString();
                    if (!dates.Contains(date))
                    {
                        dates.Add(date);
                    }
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find show. Fejl: " + e.Message);
                dates = null;
                Connection.Close();
            }
            return dates;
        }

        private Show BuildShow(string sql)
        {

            IDataReader dbReader;
            Show show = null;
            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {
                    string dateTime = (string)dbReader["showtime"];
                    int showId = int.Parse(dbReader["showid"].ToString());
                    ShowRoom showRoom = new ShowRoom((int.Parse(dbReader["showRoomNumber"].ToString())), (int.Parse(dbReader["NumberOfSeats"].ToString())));
                    Movie movie = new Movie(dbReader["title"].ToString(), dbReader["MovieLength"].ToString());
                    show = new Show(movie, showRoom, showId, dateTime);

                }
                Connection.Close();
            }
            catch (FaultException fault)
            {
                Console.WriteLine("There is no show with that id\n\n: " + fault.Message);
                show = null;
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find show. Fejl: " + e.Message);
                show = null;
                Connection.Close();
            }

            return show;
        }

        private List<Show> BuildMoreShow(string sql)
        {

            IDataReader dbReader;
            List<Show> shows = new List<Show>();


            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    String title = null;
                    int showRoomNumber = 0;
                    int showId = 0;
                    Show show = null;

                    string dateTime = (string)dbReader["showtime"];
                    title = dbReader["title"].ToString();
                    showRoomNumber = int.Parse(dbReader["showroomnumber"].ToString());
                    showId = int.Parse(dbReader["showid"].ToString());
                    ShowRoom showRoom = new ShowRoom(int.Parse(dbReader["showroomnumber"].ToString()), int.Parse(dbReader["numberofseats"].ToString()));
                    Movie movie = new Movie(dbReader["title"].ToString(), dbReader["MovieLength"].ToString());
                    show = new Show(movie, showRoom, showId, dateTime);
                    shows.Add(show);

                }
                Connection.Close();



            }
            catch (FaultException fault)
            {
                Console.WriteLine("Could not find show:\n\n " + fault.Message);
                shows = null;
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find show. Fejl: " + e.Message);
                shows = null;
                Connection.Close();
            }

            return shows;
        }

        public int getMax()
        {
            int getMax = 0;
            try
            {
                string sql = "select MAX(showid) from show";
                Connection.dbconn.Open();
                cmd = Connection.GetDBCommand(sql);
                getMax = (int)cmd.ExecuteScalar();
                getMax++;
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }

            return getMax;
        }
    }
}
